package com.larry.motion.service.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.larry.motion.service.IMotionService;
import com.larry.motion.vo.RegionType;

public abstract class MotionService implements IMotionService {

	protected String jdbcUrl = null;

	protected final String username = "rr";

	protected final String password = "repadm397";

	protected final String STUB_CONTEXT_PATH = "com.pegatron.motion.stub";

	protected final String folder = "file/";

	protected String region;

	public MotionService(String region) {
		jdbcUrl = RegionType.valueOf(region).getJdbc();
	}

//	public Smbxml processMessage(Smbxml smb, String key, ResultSet rs) throws Exception {
//		if (key.equals("RA")) {
//			Request request = new Request();
//			request.setMissingFieldOverwrite("false");
//			AddOrUpdate addOrUpdate = new AddOrUpdate();
//			Case case_ = new Case();
//			case_.setHandle(rs.getString("case_handle"));
//			case_.setCaseNumber(rs.getString("case_number"));
//
//			CustomFieldValues customFieldValues = new CustomFieldValues();
//
//			CustomFieldValue customFieldValue = new CustomFieldValue();
//			customFieldValue.setName("Request Received Date");
//			customFieldValue.setvalue(rs.getString("issue_date"));
//			customFieldValues.getCustomFieldValue().add(customFieldValue);
//
//			customFieldValue = new CustomFieldValue();
//			customFieldValue.setName("Request Received Status");
//			customFieldValue.setvalue("REQ_RECEIVED");
//			customFieldValues.getCustomFieldValue().add(customFieldValue);
//
//			customFieldValue = new CustomFieldValue();
//			customFieldValue.setName("ServiceCenterData03");
//			customFieldValue.setvalue("ACCEPTED");
//			customFieldValues.getCustomFieldValue().add(customFieldValue);
//
//			customFieldValue = new CustomFieldValue();
//			customFieldValue.setName("ServiceCenterData04");
//			customFieldValue.setvalue("The selected unit has been processed.");
//			customFieldValues.getCustomFieldValue().add(customFieldValue);
//
//			customFieldValue = new CustomFieldValue();
//			customFieldValue.setName("ServiceCenterData05");
//			customFieldValue.setvalue("N/A");
//			customFieldValues.getCustomFieldValue().add(customFieldValue);
//
//			customFieldValue = new CustomFieldValue();
//			customFieldValue.setName("ServiceCenterData06");
//			customFieldValue.setvalue("N/A");
//			customFieldValues.getCustomFieldValue().add(customFieldValue);
//
//			case_.setCustomFieldValues(customFieldValues);
//
//			addOrUpdate
//					.getAccountOrInventoryItemOrSalesTaxItemOrNonInventoryResaleItemOrOtherChargeResaleItemOrServiceResaleItemOrNonInventoryPurchItemOrNonInventorySaleItemOrOtherChargePurchItemOrOtherChargeSaleItemOrServicePurchItemOrServiceSaleItemOrDiscountItemOrMarkupItemOrSubtotalItemOrDescriptionItemOrMediaItemOrCustomerOrVendorOrEmployeeOrContactOrPartnerOrCustomFieldOrCustomListOrCustomRecordTypeOrCustomRecordOrTopicOrSolutionOrCaseOrEventOrTaskOrOtherListOrNoteOrOpportunityOrTimecardOrCustomerCategoryOrVendorCategoryOrJobTypeOrPaymentMethodOrStoreCategory()
//					.add(case_);
//			request.getAddOrAddOrUpdateOrQueryOrGet().add(addOrUpdate);
//			smb.getHeaderOrRequestOrResponse().add(request);
//			return smb;
//		} else if (key.equals("RE")) {
//			Request request = new Request();
//			request.setMissingFieldOverwrite("false");
//			AddOrUpdate addOrUpdate = new AddOrUpdate();
//			Case case_ = new Case();
//			case_.setHandle(rs.getString("case_handle"));
//			case_.setCaseNumber(rs.getString("case_number"));
//
//			CustomFieldValues customFieldValues = new CustomFieldValues();
//
//			CustomFieldValue customFieldValue = new CustomFieldValue();
//			customFieldValue.setName("Product Received Date");
//			customFieldValue.setvalue(rs.getString("receive_date"));
//			customFieldValues.getCustomFieldValue().add(customFieldValue);
//
//			case_.setCustomFieldValues(customFieldValues);
//
//			addOrUpdate
//					.getAccountOrInventoryItemOrSalesTaxItemOrNonInventoryResaleItemOrOtherChargeResaleItemOrServiceResaleItemOrNonInventoryPurchItemOrNonInventorySaleItemOrOtherChargePurchItemOrOtherChargeSaleItemOrServicePurchItemOrServiceSaleItemOrDiscountItemOrMarkupItemOrSubtotalItemOrDescriptionItemOrMediaItemOrCustomerOrVendorOrEmployeeOrContactOrPartnerOrCustomFieldOrCustomListOrCustomRecordTypeOrCustomRecordOrTopicOrSolutionOrCaseOrEventOrTaskOrOtherListOrNoteOrOpportunityOrTimecardOrCustomerCategoryOrVendorCategoryOrJobTypeOrPaymentMethodOrStoreCategory()
//					.add(case_);
//			request.getAddOrAddOrUpdateOrQueryOrGet().add(addOrUpdate);
//			smb.getHeaderOrRequestOrResponse().add(request);
//			return smb;
//		} else if (key.equals("SH")) {
//			Request request = new Request();
//			request.setMissingFieldOverwrite("false");
//			AddOrUpdate addOrUpdate = new AddOrUpdate();
//			Case case_ = new Case();
//			case_.setHandle(rs.getString("case_handle"));
//			case_.setCaseNumber(rs.getString("case_number"));
//
//			CustomFieldValues customFieldValues = new CustomFieldValues();
//
//			CustomFieldValue customFieldValue = new CustomFieldValue();
//			customFieldValue.setName("Product Shipped");
//			customFieldValue.setvalue(rs.getString("ship_date"));
//			customFieldValues.getCustomFieldValue().add(customFieldValue);
//
//			customFieldValue = new CustomFieldValue();
//			customFieldValue.setName("RMA Completed Date");
//			customFieldValue.setvalue(rs.getString("closed_date"));
//			customFieldValues.getCustomFieldValue().add(customFieldValue);
//
//			customFieldValue = new CustomFieldValue();
//			customFieldValue.setName("Shipment Tracking #");
//			customFieldValue.setvalue(rs.getString("track_no"));
//			customFieldValues.getCustomFieldValue().add(customFieldValue);
//
//			customFieldValue = new CustomFieldValue();
//			customFieldValue.setName("Quote Date");
//			customFieldValue.setvalue(rs.getString("quote_date"));
//			customFieldValues.getCustomFieldValue().add(customFieldValue);
//
//			customFieldValue = new CustomFieldValue();
//			customFieldValue.setName("QuoteApprovalDate");
//			customFieldValue.setvalue(rs.getString("quote_approval_date"));
//			customFieldValues.getCustomFieldValue().add(customFieldValue);
//
//			customFieldValue = new CustomFieldValue();
//			customFieldValue.setName("Quote Repaired Date");
//			customFieldValue.setvalue(rs.getString("quote_repaired_date"));
//			customFieldValues.getCustomFieldValue().add(customFieldValue);
//
//			// .......
//			Connection conn1 = null;
//			PreparedStatement statement1 = null;
//			ResultSet rs1 = null;
//			try {
//				// connection========================================
//				conn1 = getConnection();
//				StringBuffer buf = new StringBuffer();
//				buf.append(" select distinct m.apply_no,m.rma_center,m.rma_type,m.case_handle,m.case_number,m.case_entity_handle,m.keyin_date,m.quote_date,m.quote_approval_date,m.track_no,m.track_no_rtn,");
//				buf.append(" o.new_part_no part_no,");
//				buf.append(" (select x.short_desc from wm_part_master x where x.part_no=o.new_part_no)part_desc,");
//				buf.append(" o.problem_code,");
//				buf.append(" (RMA_UTIL_P.GETPROBLEMDESC(o.APPLY_NO, o.LINE_NO, o.RMA_CENTER, o.CODE_GROUP, o.PROBLEM_CODE))problem_desc,");
//				buf.append(" decode(elm_order.getPrice4Motion(o.new_part_no),-1,0,elm_order.getPrice4Motion(o.new_part_no))+108.7 price");
//				buf.append(" from(select a.apply_no,a.rma_center,a.rma_type,");
//				buf.append("  b.case_handle,b.case_number,b.case_entity_handle,");
//				buf.append("  (select max(x.entry_date) from rm_header_tran x where x.apply_no=a.apply_no and x.rma_center=a.rma_center and x.status='RECEIVE')keyin_date,");
//				buf.append("  (select max(x.issue_date)    from rm_motion_onhold x where x.apply_no=a.apply_no and x.rma_center=a.rma_center and x.finish='Y')quote_date,");
//				buf.append("  (select max(x.response_date) from rm_motion_onhold x where x.apply_no=a.apply_no and x.rma_center=a.rma_center and x.finish='Y')quote_approval_date,");
//				buf.append("  (select max(x.finish_date)   from rm_motion_onhold x where x.apply_no=a.apply_no and x.rma_center=a.rma_center and x.finish='Y')quote_repaired_date,");
//				buf.append("  (select x.track_no from rm_forwarder_tracking x where x.rma_no=a.rma_no and x.rma_center=a.rma_center and x.status='CSOSHIP' and x.direct_type='OUT' and rownum=1)track_no,");
//				buf.append("  (select x.track_no from rm_forwarder_tracking x where x.rma_no=a.rma_no and x.rma_center=a.rma_center and x.status='CSOSHIP' and x.direct_type='IN' and rownum=1)track_no_rtn");
//				buf.append("  from rm_header a,rm_motion_header b");
//				buf.append("  where a.delete_no is null");
//				buf.append("  and a.apply_no=b.apply_no and a.rma_center=b.rma_center");
//				buf.append("  and a.rma_center=" + rs.getString("rma_center"));
//				buf.append("  and a.apply_no=" + rs.getString("apply_no"));
//				buf.append(" )m,rm_item_detail n,rm_repair_detail o");
//				buf.append(" where n.delete_no is null");
//				buf.append(" and m.apply_no=n.apply_no and m.rma_center=n.rma_center");
//				buf.append(" and n.apply_no=o.apply_no and n.rma_center=o.rma_center and n.line_no=o.line_no");
//				buf.append(" and (o.new_part_no is not null or (o.status='REPAIR' and o.problem_code is not null))");
//
//				statement1 = conn1.prepareStatement(buf.toString());
//				rs1 = statement1.executeQuery();
//				int i = 0;
//				while (rs1.next()) {
//					i++;
//					String num = (i <= 9) ? "0" + (i) : "" + (i);
//					customFieldValue = new CustomFieldValue();
//					customFieldValue.setName("rd_partnum" + num);
//					customFieldValue.setvalue(rs1.getString("part_no"));
//					customFieldValues.getCustomFieldValue().add(customFieldValue);
//
//					customFieldValue = new CustomFieldValue();
//					customFieldValue.setName("rd_desc" + num);
//					customFieldValue.setvalue(rs1.getString("part_desc"));
//					customFieldValues.getCustomFieldValue().add(customFieldValue);
//
//					customFieldValue = new CustomFieldValue();
//					customFieldValue.setName("rd_FailureCode" + num);
//					customFieldValue.setvalue(rs1.getString("problem_code"));
//					customFieldValues.getCustomFieldValue().add(customFieldValue);
//
//					customFieldValue = new CustomFieldValue();
//					customFieldValue.setName("rd_pegatron_failure" + num);
//					customFieldValue.setvalue(rs1.getString("problem_code"));
//					customFieldValues.getCustomFieldValue().add(customFieldValue);
//
//					customFieldValue = new CustomFieldValue();
//					customFieldValue.setName("rd_TechComment" + num);
//					customFieldValue.setvalue(rs1.getString("problem_desc"));
//					customFieldValues.getCustomFieldValue().add(customFieldValue);
//
//					customFieldValue = new CustomFieldValue();
//					customFieldValue.setName("rd_price" + num);
//					customFieldValue.setvalue("0");
//					customFieldValues.getCustomFieldValue().add(customFieldValue);
//				}
//			} catch (Exception e) {
//				e.printStackTrace();
//			} finally {
//				closeConnection(conn1, statement1, rs1);
//			}
//
//			case_.setCustomFieldValues(customFieldValues);
//
//			addOrUpdate
//					.getAccountOrInventoryItemOrSalesTaxItemOrNonInventoryResaleItemOrOtherChargeResaleItemOrServiceResaleItemOrNonInventoryPurchItemOrNonInventorySaleItemOrOtherChargePurchItemOrOtherChargeSaleItemOrServicePurchItemOrServiceSaleItemOrDiscountItemOrMarkupItemOrSubtotalItemOrDescriptionItemOrMediaItemOrCustomerOrVendorOrEmployeeOrContactOrPartnerOrCustomFieldOrCustomListOrCustomRecordTypeOrCustomRecordOrTopicOrSolutionOrCaseOrEventOrTaskOrOtherListOrNoteOrOpportunityOrTimecardOrCustomerCategoryOrVendorCategoryOrJobTypeOrPaymentMethodOrStoreCategory()
//					.add(case_);
//			request.getAddOrAddOrUpdateOrQueryOrGet().add(addOrUpdate);
//			smb.getHeaderOrRequestOrResponse().add(request);
//
//			return smb;
//		} else if (key.equals("QA")) {
//			Request request = new Request();
//			request.setMissingFieldOverwrite("false");
//			AddOrUpdate addOrUpdate = new AddOrUpdate();
//			Case case_ = new Case();
//			case_.setHandle(rs.getString("case_handle"));
//			case_.setCaseNumber(rs.getString("case_number"));
//
//			CustomFieldValues customFieldValues = new CustomFieldValues();
//
//			CustomFieldValue customFieldValue = new CustomFieldValue();
//			customFieldValue.setName("QuoteApprovalAcks");
//			customFieldValue.setvalue("REQ_RECEIVED");
//			customFieldValues.getCustomFieldValue().add(customFieldValue);
//
//			customFieldValue = new CustomFieldValue();
//			customFieldValue.setName("QuoteApprovalDate");
//			customFieldValue.setvalue(rs.getString("response_date"));
//			customFieldValues.getCustomFieldValue().add(customFieldValue);
//
//			customFieldValue = new CustomFieldValue();
//			customFieldValue.setName("QuoteApprovalStatus");
//			customFieldValue.setvalue("ACCEPTED");
//			customFieldValues.getCustomFieldValue().add(customFieldValue);
//
//			customFieldValue = new CustomFieldValue();
//			customFieldValue.setName("QuoteApprovalField");
//			customFieldValue.setvalue("");
//			customFieldValues.getCustomFieldValue().add(customFieldValue);
//
//			case_.setCustomFieldValues(customFieldValues);
//
//			addOrUpdate
//					.getAccountOrInventoryItemOrSalesTaxItemOrNonInventoryResaleItemOrOtherChargeResaleItemOrServiceResaleItemOrNonInventoryPurchItemOrNonInventorySaleItemOrOtherChargePurchItemOrOtherChargeSaleItemOrServicePurchItemOrServiceSaleItemOrDiscountItemOrMarkupItemOrSubtotalItemOrDescriptionItemOrMediaItemOrCustomerOrVendorOrEmployeeOrContactOrPartnerOrCustomFieldOrCustomListOrCustomRecordTypeOrCustomRecordOrTopicOrSolutionOrCaseOrEventOrTaskOrOtherListOrNoteOrOpportunityOrTimecardOrCustomerCategoryOrVendorCategoryOrJobTypeOrPaymentMethodOrStoreCategory()
//					.add(case_);
//			request.getAddOrAddOrUpdateOrQueryOrGet().add(addOrUpdate);
//			smb.getHeaderOrRequestOrResponse().add(request);
//			return smb;
//		} else if (key.equals("CA")) {
//			Request request = new Request();
//			request.setMissingFieldOverwrite("false");
//			AddOrUpdate addOrUpdate = new AddOrUpdate();
//			Case case_ = new Case();
//			case_.setHandle(rs.getString("case_handle"));
//			case_.setCaseNumber(rs.getString("case_number"));
//
//			CustomFieldValues customFieldValues = new CustomFieldValues();
//
//			CustomFieldValue customFieldValue = new CustomFieldValue();
//			customFieldValue.setName("Request Received Date");
//			customFieldValue.setvalue(rs.getString("issue_date"));
//			customFieldValues.getCustomFieldValue().add(customFieldValue);
//
//			customFieldValue = new CustomFieldValue();
//			customFieldValue.setName("Request Received Status");
//			customFieldValue.setvalue("REQ_RECEIVED");
//			customFieldValues.getCustomFieldValue().add(customFieldValue);
//
//			customFieldValue = new CustomFieldValue();
//			customFieldValue.setName("ServiceCenterData03");
//			customFieldValue.setvalue("ACCEPTED");
//			customFieldValues.getCustomFieldValue().add(customFieldValue);
//
//			customFieldValue = new CustomFieldValue();
//			customFieldValue.setName("ServiceCenterData04");
//			customFieldValue.setvalue("The selected unit has been processed.");
//			customFieldValues.getCustomFieldValue().add(customFieldValue);
//
//			customFieldValue = new CustomFieldValue();
//			customFieldValue.setName("ServiceCenterData05");
//			customFieldValue.setvalue("N/A");
//			customFieldValues.getCustomFieldValue().add(customFieldValue);
//
//			customFieldValue = new CustomFieldValue();
//			customFieldValue.setName("ServiceCenterData06");
//			customFieldValue.setvalue("N/A");
//			customFieldValues.getCustomFieldValue().add(customFieldValue);
//
//			case_.setCustomFieldValues(customFieldValues);
//
//			addOrUpdate
//					.getAccountOrInventoryItemOrSalesTaxItemOrNonInventoryResaleItemOrOtherChargeResaleItemOrServiceResaleItemOrNonInventoryPurchItemOrNonInventorySaleItemOrOtherChargePurchItemOrOtherChargeSaleItemOrServicePurchItemOrServiceSaleItemOrDiscountItemOrMarkupItemOrSubtotalItemOrDescriptionItemOrMediaItemOrCustomerOrVendorOrEmployeeOrContactOrPartnerOrCustomFieldOrCustomListOrCustomRecordTypeOrCustomRecordOrTopicOrSolutionOrCaseOrEventOrTaskOrOtherListOrNoteOrOpportunityOrTimecardOrCustomerCategoryOrVendorCategoryOrJobTypeOrPaymentMethodOrStoreCategory()
//					.add(case_);
//			request.getAddOrAddOrUpdateOrQueryOrGet().add(addOrUpdate);
//			smb.getHeaderOrRequestOrResponse().add(request);
//			return smb;
//		} 
//		throw new Exception("invalid key !!");
//	}

	protected Connection getConnection() {
		Connection conn = null;
		try {
			DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
			conn = DriverManager.getConnection(jdbcUrl, username, password);
			System.out.println("JDBC: connected to \n" + (conn != null ? jdbcUrl : "100") + "!\n");
		} catch (SQLException e) {
			e.printStackTrace();
			System.out.println("ERROR: failed to connect!\n" + e.toString());
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("ERROR: failed to connect!\n" + e.toString());
		}
		return conn;
	}

	protected void closeConnection(Connection connection, Statement statement, ResultSet result) {
		try {
			if (result != null) {
				result.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			result = null;
		} finally {
			try {
				if (result != null) {
					result.close();
				}
			} catch (Exception e) {
				result = null;
			}
		}
		try {
			if (statement != null) {
				statement.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			statement = null;
		} finally {
			try {
				if (statement != null) {
					statement.close();
				}
			} catch (Exception e) {
				statement = null;
			}
		}
		try {
			if (connection != null) {
				connection.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
			connection = null;
		} finally {
			try {
				if (connection != null) {
					connection.close();
				}
			} catch (Exception e) {
				connection = null;
			}
		}
	}
}
